CREATE OR REPLACE PACKAGE GETINSTALLBASEDATA AS 
   PROCEDURE POPULATEINSTALLBASEINFO; 
   PROCEDURE POPULATEINSTALLBASEADDRESSES; 
   PROCEDURE POPULATEINSTALLBASECONTACTS; 
   PROCEDURE POPULATEINSTALLBASENOTES; 
   PROCEDURE POPULATEINSTALLBASEWARRANRTY;
END GETINSTALLBASEDATA; 
/

CREATE OR REPLACE PACKAGE BODY GETINSTALLBASEDATA AS 
 
   PROCEDURE POPULATEINSTALLBASEINFO AS 
   BEGIN 
     INSERT INTO TBL_STAGING_INSTALL_BASE_INFO 
        SELECT INSTALL_BASE_SEQ.NEXTVAL,
	CSI.INSTANCE_ID,
	CSI.OWNER_PARTY_ID,
	CSI.CREATION_DATE,
	CSI.LAST_OE_ORDER_LINE_ID,
	CSI.INSTALL_DATE,
	CSI.QUANTITY,
	TO_NUMBER(SUBSTR(CSI.ATTRIBUTE2,1,4)) SELLING_STORE,
	CSI.ATTRIBUTE3 POS_TRANS_DATE,
	CSI.ATTRIBUTE4 POS_REGISTER_ID,
	CSI.ATTRIBUTE5 POS_TRANS_ID,
	CSI.ATTRIBUTE7 SELLING_PRICE,
	CSI.ATTRIBUTE10 LIFE_EXPECTANCY,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	ITEMS.SEGMENT1,
	ITEMS.DESCRIPTION,
	ITEMS.ITEM_TYPE,
	MC.SEGMENT4,
	USER1.USER_NAME
	FROM 
	CSI.CSI_ITEM_INSTANCES CSI,
	FND_USER USER1,	
	MTL_SYSTEM_ITEMS_B ITEMS,
	MTL_ITEM_CATEGORIES MTC,
	MTL_CATEGORIES_B MC
	WHERE 
	(CSI.ACTIVE_END_DATE is null or CSI.ACTIVE_END_DATE >SYSDATE)
	AND CSI.LAST_OE_ORDER_LINE_ID IS NULL
	AND CSI.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID(+) 
	AND ITEMS.ORGANIZATION_ID=22 
	AND ITEMS.INVENTORY_ITEM_ID = MTC.INVENTORY_ITEM_ID(+)
	AND MTC.ORGANIZATION_ID = 22
	AND MTC.CATEGORY_ID = MC.CATEGORY_ID(+)
	AND MTC.CATEGORY_SET_ID = 1	
	AND USER1.USER_ID(+)=CSI.CREATED_BY;

     COMMIT;

     INSERT INTO TBL_STAGING_INSTALL_BASE_INFO 
        SELECT INSTALL_BASE_SEQ.NEXTVAL,
	CSI.INSTANCE_ID,
	CSI.OWNER_PARTY_ID,
	CSI.CREATION_DATE,
	CSI.LAST_OE_ORDER_LINE_ID,
	CSI.INSTALL_DATE,
	CSI.QUANTITY,
	TO_NUMBER(SUBSTR(CSI.ATTRIBUTE2,1,4)) SELLING_STORE,
	CSI.ATTRIBUTE3 POS_TRANS_DATE,
	CSI.ATTRIBUTE4 POS_REGISTER_ID,
	CSI.ATTRIBUTE5 POS_TRANS_ID,
	CSI.ATTRIBUTE7 SELLING_PRICE,
	CSI.ATTRIBUTE10 LIFE_EXPECTANCY,
	HEADER.ORDER_NUMBER,
	HEADER.ORDER_TYPE_ID,
	LINES.HEADER_ID,
	LINES.SHIP_TO_ORG_ID,
	LINES.SHIP_FROM_ORG_ID,
	LINES.SHIPPING_METHOD_CODE,
	HRU.NAME,	
	ITEMS.SEGMENT1,
	ITEMS.DESCRIPTION,
	ITEMS.ITEM_TYPE,
	MC.SEGMENT4,
	USER1.USER_NAME
	FROM 
	CSI.CSI_ITEM_INSTANCES CSI,
	FND_USER USER1,
	OE_ORDER_LINES_ALL LINES,
	OE_ORDER_HEADERS_ALL HEADER,
	MTL_SYSTEM_ITEMS_B ITEMS,
	MTL_ITEM_CATEGORIES MTC,
	MTL_CATEGORIES_B MC,
	HR_ORGANIZATION_UNITS HRU
	WHERE 
	(CSI.ACTIVE_END_DATE is null or CSI.ACTIVE_END_DATE >SYSDATE)
	AND CSI.LAST_OE_ORDER_LINE_ID = LINES.LINE_ID(+) 
	AND LINES.HEADER_ID=HEADER.HEADER_ID(+)
	AND HEADER.ORDER_TYPE_ID != 1009
	AND CSI.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID(+) 
	AND ITEMS.ORGANIZATION_ID=22 
	AND ITEMS.INVENTORY_ITEM_ID = MTC.INVENTORY_ITEM_ID(+)
	AND MTC.ORGANIZATION_ID = 22
	AND MTC.CATEGORY_ID = MC.CATEGORY_ID(+)
	AND MTC.CATEGORY_SET_ID = 1	
	AND USER1.USER_ID(+)=CSI.CREATED_BY
	AND LINES.SHIP_FROM_ORG_ID=HRU.ORGANIZATION_ID(+);

      COMMIT;

   END POPULATEINSTALLBASEINFO; 

   PROCEDURE POPULATEINSTALLBASEADDRESSES AS 
   BEGIN 
     INSERT INTO TBL_STAGING_INSTALL_BASE_ADDR
        SELECT CSI.INSTANCEID,NVL((LOC.ADDRESS1||LOC.ADDRESS2||LOC.ADDRESS3||LOC.ADDRESS4),' ')ADDRESS,
	LOC.CITY, 
	NVL(LOC.PROVINCE,LOC.STATE),
	LOC.POSTAL_CODE ZIPCODE
	FROM
	TBL_STAGING_INSTALL_BASE_INFO CSI,
	HZ_PARTY_SITES PS,
	AR.HZ_LOCATIONS LOC 
	WHERE 
	CSI.OWNERPARTYID=PS.PARTY_ID(+)
	AND PS.LOCATION_ID=LOC.LOCATION_ID(+) 
	AND PS.IDENTIFYING_ADDRESS_FLAG = 'Y'  ;

      COMMIT;      

   END POPULATEINSTALLBASEADDRESSES; 

   PROCEDURE POPULATEINSTALLBASECONTACTS AS 
   BEGIN 
     INSERT INTO TBL_STAGING_INSTALL_BASE_CONT
	SELECT CSI.INSTANCEID,
	P.PERSON_FIRST_NAME,
	P.PERSON_LAST_NAME,
	DECODE(CP.PHONE_LINE_TYPE,'MOBILE',CP.PHONE_NUMBER,' ') MOBILE,
	DECODE(CP.PHONE_LINE_TYPE,'FAX',CP.PHONE_NUMBER,' ') FAX,
	-- Start of Area code added to the phone no - Ganesh-29-Jun-2010
	DECODE(CP.PHONE_LINE_TYPE,'OFFICE:',DECODE( CP.PHONE_AREA_CODE, NULL,'',CP.PHONE_AREA_CODE||'-')||CP.PHONE_NUMBER,' ') DAY_PHONE,
	DECODE(CP.PHONE_LINE_TYPE,'HOME:',DECODE( CP.PHONE_AREA_CODE, NULL,'',CP.PHONE_AREA_CODE||'-')||CP.PHONE_NUMBER,' ') HOME_PHONE,
	-- End of Area code added to the phone no - Ganesh-29-Jun-2010
	DECODE(ECP.CONTACT_POINT_TYPE,'EMAIL',ECP.EMAIL_ADDRESS,' ') EMAIL_ADDRESS
	FROM 
	TBL_STAGING_INSTALL_BASE_INFO CSI,
	HZ_PARTY_SITES PS,
	HZ_CUST_SITE_USES_ALL CSU,
	HZ_CUST_ACCT_SITES_ALL CAS,
	HZ_CONTACT_POINTS CP,
	OE_ORDER_LINES_ALL LINES,
	HZ_CONTACT_POINTS ECP,
	AR.HZ_PARTIES P
	WHERE 
	CSI.OWNERPARTYID=P.PARTY_ID(+) and
	CSI.LINEID = LINES.LINE_ID(+) 
	AND LINES.INVOICE_TO_ORG_ID = CSU.SITE_USE_ID(+)
	AND CSU.SITE_USE_CODE(+) = 'BILL_TO' 
	AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID(+) 
	AND CAS.PARTY_SITE_ID=PS.PARTY_SITE_ID(+)  
	AND PS.PARTY_ID = CP.OWNER_TABLE_ID(+)  
	AND CP.OWNER_TABLE_NAME(+)  = 'HZ_PARTIES'  
	AND CP.STATUS(+)  = 'A' 
	AND CP.PRIMARY_FLAG(+)  = 'Y'  
	AND CP.CONTACT_POINT_TYPE(+) = 'PHONE' 
	AND CP.OWNER_TABLE_ID =ECP.OWNER_TABLE_ID(+)  
	AND ECP.OWNER_TABLE_NAME(+)  = 'HZ_PARTIES'  
	AND ECP.STATUS(+)  = 'A' 
	AND ECP.PRIMARY_FLAG(+)  = 'Y'  
	AND ECP.CONTACT_POINT_TYPE(+)='EMAIL' ;

      COMMIT;
  

   END POPULATEINSTALLBASECONTACTS; 

   PROCEDURE POPULATEINSTALLBASENOTES AS 
   BEGIN 
     INSERT INTO TBL_STAGING_INSTALL_BASE_NOTES
	SELECT 
	CSI.INSTANCEID,
	JNT.CREATION_DATE,
	JNT.NOTES,
	USER1.USER_NAME
	FROM 
	TBL_STAGING_INSTALL_BASE_INFO CSI,
	JTF_NOTES_B JN,  
	JTF_NOTES_TL JNT, 
	FND_USER USER1
	WHERE  
	CSI.INSTANCEID =JN.SOURCE_OBJECT_ID(+)
	AND JN.JTF_NOTE_ID = JNT.JTF_NOTE_ID(+)
	AND  JNT.CREATED_BY =USER1.USER_ID(+)
	AND JNT.LANGUAGE = 'US';

      COMMIT;
    

   END POPULATEINSTALLBASENOTES; 

   PROCEDURE POPULATEINSTALLBASEWARRANRTY AS 
   BEGIN 
     INSERT INTO TBL_STAGING_INSTALL_BASE_WTY
	SELECT
	CSI.INSTANCEID,
	HEADER.CONTRACT_NUMBER,
	HEADER.START_DATE,
	HEADER.END_DATE,
	HEADER.STS_CODE,
	HEADER.CREATION_DATE,
	HEADER.ESTIMATED_AMOUNT HEADERCONTRACTAMT,
	HEADER.ATTRIBUTE10 HEADERGST,
	HEADER.ATTRIBUTE11 HEADERPST,
	SUBLINE_STYLES.LTY_CODE WTYTYPE,
	LINES.START_DATE LINESTARTDATE,
	LINES.END_DATE LINEENDDATE,
	LINES.DATE_TERMINATED LINETERMINATIONDATE,
	LINES.PRICE_UNIT WTYPRICE,
	MTL.SEGMENT1 WTYSKU,
	MTL.DESCRIPTION WTYDESCRIPTION,
	MC.SEGMENT4
	FROM 
	TBL_STAGING_INSTALL_BASE_INFO CSI,
	OKC_K_ITEMS ITEMS,
	OKC_K_ITEMS SKU_ITEMS,
	OKC_K_LINES_B LINES,
	OKC_K_HEADERS_B HEADER,
	OKC_LINE_STYLES_V LINE_STYLES,
	OKC_LINE_STYLES_V SUBLINE_STYLES,
	MTL_SYSTEM_ITEMS_B MTL,
	MTL_ITEM_CATEGORIES MTC,
	MTL_CATEGORIES_B MC
	WHERE
	CSI.INSTANCEID = ITEMS.OBJECT1_ID1(+)
	AND ITEMS.JTOT_OBJECT1_CODE ='OKX_CUSTPROD'
	AND ITEMS.DNZ_CHR_ID = HEADER.ID(+)
	AND HEADER.SCS_CODE = 'WARRANTY'
	AND ITEMS.CLE_ID = LINES.ID(+)
	AND LINES.STS_CODE IN ('ACTIVE','SIGNED','EXPIRED')
	AND LINES.LSE_ID = LINE_STYLES.ID(+)
	AND LINE_STYLES.LTY_CODE = 'COVER_PROD'
	AND LINE_STYLES.LSE_PARENT_ID = SUBLINE_STYLES.ID(+)
	AND SUBLINE_STYLES.LTY_CODE IN ('EXT_WARRANTY', 'WARRANTY')
	AND LINES.CLE_ID= SKU_ITEMS.CLE_ID(+)
	AND SKU_ITEMS.OBJECT1_ID1=MTL.INVENTORY_ITEM_ID(+)
	AND MTL.ORGANIZATION_ID = 22
	AND MTL.INVENTORY_ITEM_ID = MTC.INVENTORY_ITEM_ID(+)
	AND MTC.ORGANIZATION_ID = 22
	AND MTC.CATEGORY_ID = MC.CATEGORY_ID(+)
	AND MTC.CATEGORY_SET_ID = 1;

      COMMIT;

   END POPULATEINSTALLBASEWARRANRTY; 
END GETINSTALLBASEDATA; 
/ 
